package bg.cinema_system.DAO;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import bg.cinema_system.beans.MovieBean;
import bg.cinema_system.global.ConnectionJDBC;
import bg.cinema_system.pojo.Movie;

public class MovieDAO {

	// Connect to DB and get information about the selected Movie
	public static MovieBean getMovie(int movie_id) throws SQLException {
		// Get database connection
		Connection dbConnection = ConnectionJDBC.getConnection();

		if (dbConnection == null) {
			throw new SQLException(
					"Class MovieDAO: Can't get database connection");
		}

		String sqlStatement = "SELECT movies.movie_id, movies.name, movies.movie_type, movies.duration, movies.director, movies.actors, movies.country, movies.summary "
				+ "FROM movies " + "WHERE movies.movie_id = ?";

		PreparedStatement ps = null;
		ResultSet result = null;
		MovieBean movie = new MovieBean();
		try {
			ps = dbConnection.prepareStatement(sqlStatement);
			ps.setInt(1, movie_id);
			
			// Get customer data from database
			result = ps.executeQuery();
			if (result.next()) {
				movie.setMovie_id(result.getInt("movie_id"));
				movie.setName(result.getString("name"));
				movie.setMovie_type(result.getString("movie_type"));
				movie.setDuration(result.getTime("duration"));
				movie.setDirector(result.getString("director"));
				movie.setActors(result.getString("actors"));
				movie.setCountry(result.getString("country"));
				movie.setSummary(result.getString("summary"));
			}
		} catch (SQLException e) {
			throw new SQLException(e);
		} finally {
			try {
				// Close all connections
				ps.close();
				result.close();
				dbConnection.close();
			} catch (SQLException e) {
				throw new SQLException(e);
			}
		}

		return movie;
	}
	
	public static List<String> getAllMovieNames() throws SQLException {
		// Get database connection
		Connection dbConnection = ConnectionJDBC.getConnection();

		if (dbConnection == null) {
			throw new SQLException(
					"Class MovieDAO: Can't get database connection");
		}
		
		String sql = "SELECT movie.name FROM cinema.movies movie ORDER BY movie.name ASC";
		PreparedStatement ps = null;
		ResultSet result = null;
		List<String> movieNames = new ArrayList<String>();
		try {
			ps = dbConnection.prepareStatement(sql);
		
			result = ps.executeQuery();
			
			while(result.next()){
				movieNames.add(result.getString("name"));
			}
		} catch (SQLException e) {
			throw new SQLException(e);
		} finally {
			try {
				// Close all connections
				ps.close();
				result.close();
				dbConnection.close();
			} catch (SQLException e) {
				throw new SQLException(e);
			}
		}
		
		return movieNames;

	}
	
	public static void insertMovie(Movie movie) throws SQLException, ParseException{
		Date date =  new SimpleDateFormat("HH:mm:ss").parse(movie.getDuration());
		Connection conn = ConnectionJDBC.getConnection();
		PreparedStatement prepareStatement = null;
		try {
			prepareStatement = conn
					.prepareStatement("INSERT INTO Movies (name, movie_type, duration, director, actors, country, summary) VALUES  (?, ?, ?, ?, ?, ?, ?)");
			prepareStatement.setString(1, movie.getName());
			prepareStatement.setString(2, movie.getMovieType());
			prepareStatement.setTime(3, new java.sql.Time(date.getTime()));
			prepareStatement.setString(4, movie.getDirector());
			prepareStatement.setString(5, movie.getActors());
			prepareStatement.setString(6, movie.getCountry());
			prepareStatement.setString(7, movie.getSummary());
			// Execute insert SQL statement
			prepareStatement.executeUpdate();
		} catch (SQLException e) {
			throw new SQLException(e);
		} finally {
			try {
				// Close all connections
				prepareStatement.close();
				conn.close();
			} catch (SQLException e) {
				throw new SQLException(e);
			}
		}
		
	}
}
